package com.sunseaypw.db.jdbcTemp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.sql.DataSource;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

public class JdbcTemp {    
    public static void main(String[] args){
        ClassPathXmlApplicationContext ctx=new ClassPathXmlApplicationContext("jdbcTemp.xml");
        var st=ctx.getBean(UserDAO.class).getStudentById(7);
        System.out.println(st.toString());
        var st2=ctx.getBean(UserDAO.class).getStudentByName("啊");
        System.out.println(st2.toString());
        var st3=ctx.getBean(UserDAO.class).getStudentByGrade(1);
        System.out.println(st3.toString());

        st3.name="newnewjkjkjk快捷键艰苦艰苦艰苦艰苦艰苦艰苦就8888888888888888888888";
        ctx.getBean(UserDAO.class).update(st3);
    }

    @Configuration
    @PropertySource("jdbc.properties")
    public static class BeanConfig{
        @Value("${jdbc.url}")
        String jdbcUrl;
        @Value("${jdbc.username}")
        String jdbcUsername;
        @Value("${jdbc.password}")
        String jdbcPassword;

        @Bean
        DataSource createDataSource(){
            HikariConfig config = new HikariConfig();
            config.setJdbcUrl(jdbcUrl);
            config.setUsername(jdbcUsername);
            config.setPassword(jdbcPassword);
            config.addDataSourceProperty("autoCommit", "true");
            config.addDataSourceProperty("connectionTimeout", "5");
            config.addDataSourceProperty("idleTimeout", "60");
            config.addDataSourceProperty("maximumPoolSize", "10"); // 最大连接数：10
            return new HikariDataSource(config);
        }

        @Bean
        JdbcTemplate createJdbcTemplate(@Autowired DataSource ds){
            return new JdbcTemplate(ds);
        }
    }
    @Component
    public static class UserDAO{
        @Autowired
        JdbcTemplate jt;
        public Student getStudentById(int id){
            return jt.execute((Connection con)->{
                try(var ps=con.prepareStatement("select * from students where id=?")){
                    ps.setInt(1, id);
                    try(var rs=ps.executeQuery()){
                        if(rs.next()){
                            var st=new Student();
                            st.id=rs.getLong("id");
                            st.name=rs.getString("name");
                            st.gender=rs.getInt("gender")>0;
                            st.grade=rs.getInt("grade");
                            return st;
                        }else{
                            throw new RuntimeException("没有查到当前id的记录");
                        }                        
                    }
                }               
            });//内部自动释放conn
        }

        public Student getStudentByName(String name){
           return jt.execute("select * from students where name like ? ", (PreparedStatement ps)->{
                ps.setString(1, '%'+name+'%');
                try(var rs = ps.executeQuery();){
                    if(rs.next()){
                        var st=new Student();
                        st.id=rs.getLong("id");
                        st.name=rs.getString("name");
                        st.gender=rs.getInt("gender")>0;
                        st.grade=rs.getInt("grade");
                        return st;
                    }else{
                        throw new RuntimeException("没有查到当前name的记录");
                    }              
                }
            });
        }

        public Student getStudentByGrade(int grade){
            return jt.queryForObject("select * from students where grade = ? limit 3", new Object[]{ grade }
                , (ResultSet rs, int rowNum)->{
                    Student st=null;
                    if(rs!=null){
                        while(rs!=null){
                            st=new Student();
                            st.id=rs.getLong("id");
                            st.name=rs.getString("name");
                            st.gender=rs.getInt("gender")>0;
                            st.grade=rs.getInt("grade");
                            System.out.println("接收到一条记录！");
                            if(!rs.next()) break;
                        }  
                        return st;                      
                    }else{
                        throw new RuntimeException("没有查到当前grade的记录");
                    }      
            });
         }
         
        public void update(Student st){
            if(1 != jt.update("update students set name=? , grade= ? where id=?", st.name,st.grade,st.id)){
                throw new RuntimeException("没有更新成功啊！");
            }
        }       

    }
    public static class Student{
        public long id;
        public String name;
        public boolean gender;
        public int grade;
        public String toString(){
            return String.format("id=%s name=%s gender=%s grade=%s ", this.id,this.name,this.gender,this.grade);
        }
    }    
}
